import sqlite3

DATABASE = './data/events.db'

def connect_db():
    return sqlite3.connect(DATABASE)

# 创建表
def create_table():
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS events (
            year INTEGER PRIMARY KEY,
            title TEXT,
            description TEXT,
            image_path TEXT,
            video_link TEXT
        )
    ''')
    conn.commit()
    conn.close()

# 插入事件
def insert_event(year, title, description, image_path, video_link):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO events (year, title, description, image_path, video_link)
        VALUES (?, ?, ?, ?, ?)
    ''', (year, title, description, image_path, video_link))
    conn.commit()
    conn.close()

# 获取所有事件
def get_all_events():
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('SELECT year, title FROM events ORDER BY year ASC')
    events = cursor.fetchall()
    conn.close()
    return events

# 获取单个事件
def get_event_by_year(year):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM events WHERE year=?', (year,))
    event = cursor.fetchone()
    conn.close()
    return event
